package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.*;

public class DBOfferMaterial implements IFDBOfferMaterial{
	private Connection connection;
	private int offerMaterialId1;
	
	public DBOfferMaterial()
	{
		connection = DbConnection.getInstance().getDBcon();
	}
	
	
	
	public ArrayList<OfferMaterial> getAllOfferMaterials() 
	{
		return miscWhere("");
	}
	
	public ArrayList<OfferMaterial> getAllOfferMaterialsConnectedToOffer(int offerId)
	{
		
		return  miscWhere("offerId="+offerId);
	}
	
	
	public OfferMaterial findOfferMaterial(int offerId)
    {   String wClause = "  offerId = " + offerId;
        return singleWhere(wClause);
    }
	
	public OfferMaterial findOfferMaterialById(int offerMaterialId)
    {   String wClause = "  offerMaterialId = " + offerMaterialId;
        return singleWhere(wClause);
    }
	
	
	

	public int deleteOfferMaterial(int offerMaterialId) {
		int rc = -1;
		
		String query = "DELETE FROM OfferMaterial WHERE offerMaterialId = "+offerMaterialId;
		
		try{
			Statement stmt = connection.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception ex){
			System.err.println("Couldn't delete the OfferLMaterial with offerLabour ID "+offerMaterialId);
		}
		
		
		return rc;
	}
	
	public int deleteOfferMaterialConnected(int offerId) {
		int rc = -1;
		
		String query = "DELETE FROM OfferMaterial WHERE offerId = "+offerId;
		System.out.println(query);
		
		try{
			Statement stmt = connection.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception ex){
			System.err.println("Couldn't delete the OfferMaterial conencted with "+offerId);
		}
		
		
		return rc;
	}
	
	
	public void generateId()
	{
	    offerMaterialId1 = GetMax.getMaxId("Select max(offerMaterialId) from OfferMaterial");
		offerMaterialId1 = offerMaterialId1 + 1;
       System.out.println("next = " +  offerMaterialId1);
	}
	
	public int getGeneratedId()
    {
        return offerMaterialId1;
    }
	
	public int insertOfferMaterial(OfferMaterial offerMaterial) throws Exception
    {   
        
		generateId();
       int rc = -1;
	   String query="INSERT INTO OfferMaterial(offerId, materialId, price, quantity, name,offerMaterialId, type)   VALUES "
				+ "("+offerMaterial.getOfferId()+","+offerMaterial.getMaterialId()+","+offerMaterial.getMaterialPrice()+","+offerMaterial.getMaterialQuantity()+",'"+offerMaterial.getMaterialName()+"'"+","+offerMaterialId1+",'"+offerMaterial.getMaterialType() +"')";

       System.out.println("insert : " + query);
      try{ 
          Statement stmt = connection.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
      }
       catch(SQLException ex){
          System.out.println("Material insert Error");
          throw new Exception ("Error");
       }
       return(rc);
    }

	
	public int updateOfferMaterial(OfferMaterial offerMaterial) {
		OfferMaterial materialObj  = offerMaterial;
		int rc = -1;
		
		String query1 =  "UPDATE OfferMaterial SET offerId = "+offerMaterial.getOfferId() +", materialId="+offerMaterial.getMaterialId()+", price = "+offerMaterial.getMaterialPrice()+", quantity = "+offerMaterial.getMaterialQuantity()+",name ='"+offerMaterial.getMaterialName()+"'"+", offerMaterialId = "+offerMaterial.getOfferMaterialId()+",type ='"+offerMaterial.getMaterialType()+"'"+" WHERE offerMaterialId = "+ materialObj.getOfferMaterialId();
		System.out.println(query1);
		try{
			Statement stmt = connection.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query1);
			
			stmt.close();
		}
		catch(Exception ex){
			System.err.println("Couldn't update");
		}
		
		
		return rc;
	}
	

	
	private OfferMaterial singleWhere(String wClause)
	{
		OfferMaterial offerMaterial = new OfferMaterial();
		
		ResultSet results;
		String query = buildQuery(wClause);
		
		
		try{
			Statement stmt = connection.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if(results.next())
				offerMaterial = buildOfferMaterial(results);
			
			stmt.close();
		}
		catch(Exception ex){}
		
		return offerMaterial;
	}
	

	
	private ArrayList<OfferMaterial> miscWhere(String wClause)
	{
		ArrayList<OfferMaterial> offerMaterials = new ArrayList<>();
		
		ResultSet results;
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = connection.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			
			while(results.next())
			{
				OfferMaterial offerMaterial = buildOfferMaterial(results);
				offerMaterials.add(offerMaterial);
			}
			
			stmt.close();
			
		}
		catch(Exception ex){}
		return offerMaterials;
	}
	
    
	
	
	
	 private OfferMaterial buildOfferMaterial(ResultSet results){
	        OfferMaterial offerMaterialObj = new OfferMaterial();
	          try{
	        	  	offerMaterialObj.setOfferId(results.getInt("offerId"));
	        	  	offerMaterialObj.setMaterialId(results.getInt("materialId"));
	        	  	offerMaterialObj.setMaterialPrice(results.getDouble("price"));
	        	  	offerMaterialObj.setMaterialQuantity(results.getInt("quantity"));
	        	  	offerMaterialObj.setMaterialName(results.getString("name"));
	        	  	offerMaterialObj.setMaterialType(results.getString("type"));
	               offerMaterialObj.setOfferMaterialId(results.getInt("offerMaterialId"));
	          }
	         catch(Exception e)
	         {
	             System.out.println("Cannot create OfferMaterial object.");
	         }
	         return offerMaterialObj;
	      
	    }
	
	
	private String buildQuery(String wClause)
	{
		String query = "SELECT offerId, materialId, price, quantity, name, offerMaterialId, type FROM OfferMaterial";
		
		if(wClause.length() > 0) query = query +" WHERE "+ wClause;
		
		return query;
	}



}
